package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import db.DBDruidManager;
import utils.JDBCUtil;
import utils.Log4jInit;

public class Query {
    /**
     * 增、删、改 JDBC操作的模板方法
     *
     * @param sql    sql语句
     * @param params sql语句的参数
     * @return sql语句影响的行数，若出错则返回-1.
     */
    public static int executeDML(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = (Connection) DBDruidManager.getConnection();
//            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            JDBCUtil.handleParamsForPreparedStatement(ps, params);
//            conn.commit();
            return ps.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
           
           // DBManager.closeConnection(conn, null, ps, null);
            DBDruidManager.closeConnection();
        }
    }

    public static List<Object> executeDMLwithGeneratedKeys(List<Object> results, String sql, Object[] params, QueryCallBackList back) {
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            conn = DBDruidManager.getConnection();
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            JDBCUtil.handleParamsForPreparedStatement(ps, params);
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            return back.doExecuteList(rs, results);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
         //   DBManager.closeConnection(conn, null, ps, null);
        	   DBDruidManager.closeConnection();
        }
    }

    /**
     * 执行JDBC查询操作 的模板方法，通过回掉实现对查询返回对象的封装。
     *
     * @param sql    sql语句
     * @param params sql参数
     * @param back   QueryCallBack的实现类，实现回掉。
     * @return
     */
    public static List<Object> executeQueryList(List<Object> results, String sql, Object[] params, QueryCallBackList back) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = DBDruidManager.getConnection();
            ps = conn.prepareStatement(sql);
            JDBCUtil.handleParamsForPreparedStatement(ps, params);
            rs = ps.executeQuery();
            return back.doExecuteList(rs, results);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
       //     DBManager.closeConnection(conn, null, ps, rs);
            DBDruidManager.closeConnection();
        }
    }

    /**
     * 执行JDBC查询操作 的模板方法，通过回掉实现对查询返回对象的封装。
     *
     * @param sql    sql语句
     * @param params sql参数
     * @param back   QueryCallBack的实现类，实现回掉。
     * @return
     */
    public static Object executeQueryObject(String sql, Object[] params, QueryCallBack back) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            conn = DBDruidManager.getConnection();
            ps = conn.prepareStatement(sql);
            JDBCUtil.handleParamsForPreparedStatement(ps, params);
            rs = ps.executeQuery();
            return back.doExecute(rs);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
        //    DBManager.closeConnection(conn, null, ps, rs);
            DBDruidManager.closeConnection();
        }
    }

    /**
     * 批量进行增、删、改 JDBC操作的模板方法
     *
     * @param sql sql语句
     * @return sql语句影响的行数，若出错则返回-1.
     */
    public static int executeBatchDML(String sql, ArrayList<Object[]> paramList) {
        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;
        try {
            conn = DBDruidManager.getConnection();
            if (paramList == null) {
                return conn.prepareStatement(sql).executeUpdate();
            } else {
                conn.setAutoCommit(false);
                ps = conn.prepareStatement(sql);
                for (int i = 0; i < paramList.size(); i++) {
                    JDBCUtil.handleParamsForPreparedStatement(ps, paramList.get(i));
                    ps.addBatch();
                }
                int[] results = ps.executeBatch();
                conn.commit();
                for (int i = 0; i < results.length; i++) result += results[i];
                return result == paramList.size() ? result : (-1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        //    DBManager.closeConnection(conn, null, ps, null);
            DBDruidManager.closeConnection();
        }
    }

    @Test
    public void executeDMLTest() {
        String sql = "select * from algorithm";
        Connection conn = null;
        PreparedStatement ps = null;
        Log4jInit.logger.info("%d",Connection.TRANSACTION_READ_COMMITTED);
        try {
            conn = DBDruidManager.getConnection();
            conn.setTransactionIsolation(8);
            int isolation = conn.getTransactionIsolation();
            Log4jInit.logger.info("isolation:"+isolation);
//            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            JDBCUtil.handleParamsForPreparedStatement(ps, null);
//            conn.commit();
            Log4jInit.logger.info("result:"+ps.executeUpdate());

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
       //     DBManager.closeConnection(conn, null, ps, null);
            DBDruidManager.closeConnection();
        }
    }

}

